﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;
using System.Drawing;
using System.Configuration;
using Blank.CommonLib.Encrypt;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;
using System.Runtime.Serialization;
using Blank.Data.Models;
using Blank.Data.Enumrator;

namespace Blank.Data
{
    public static class CommonDbHelper
    {
        private static string _connectionString;
        public static string ConnectionString
        {
            get
            {
                if (_connectionString == null)
                {
                    _connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                    if (IsEncrypt)
                    {
                        _connectionString = MD5Util.Md5Decrypt(_connectionString);
                    }
                }
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }
        private static DbConnection _connction;
        public static DbConnection Connction
        {
            get
            {
                if (_connction == null)
                    _connction = GetConnection(ConnectionString);
                return CommonDbHelper._connction;
            }
            set
            {
                CommonDbHelper._connction = value;
            }
        }
        /// <summary>
        /// 是否有进行加密
        /// </summary>
        public static bool IsEncrypt
        {
            get
            {
                string _IsEncrypt = ConfigurationManager.AppSettings["IsEncrypt"];
                if ("true".Equals(_IsEncrypt))
                {
                    return true;
                }
                return false;
            }
        }
        private static DataBaseType dbType;
        /// <summary>
        /// 数据库类型
        /// </summary>
        public static DataBaseType DBType
        {
            get
            {
                try
                {
                    string _dbType = ConfigurationManager.AppSettings["DbType"];
                    if (IsEncrypt)
                    {
                        _dbType = MD5Util.Md5Decrypt(_dbType);
                    }
                    if (Enum.IsDefined(typeof(DbType), _dbType))
                        dbType = (DataBaseType)Enum.Parse(typeof(DbType), _dbType);
                    else
                        dbType = DataBaseType.SqlServer;

                    return dbType;
                }
                catch
                {
                    return DataBaseType.SqlServer;
                }
            }
            set
            {
                dbType = value;
            }
        }
        /// <summary>
        /// 获得连接对象
        /// </summary>
        /// <returns></returns>
        public static DbConnection GetConnection(string connectionString)
        {
            try
            {
                DbConnection Connection = null;
                if (string.IsNullOrEmpty(connectionString))
                    throw new ArgumentNullException("connectionString is null value!");
                switch (DBType)
                {
                    case DataBaseType.OleDb:
                        Connection = new OleDbConnection(connectionString);
                        break;
                    case DataBaseType.Oracle:
                        Connection = new OracleConnection(connectionString);
                        break;
                    case DataBaseType.SqlServer:
                        Connection = new SqlConnection(connectionString);
                        break;
                    case DataBaseType.MySql:
                        Connection = new MySqlConnection(connectionString);
                        break;
                    default:
                        break;
                }
                return Connection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #region DbCommand
        /// <summary>
        /// 获取对数据源执行的SQL语句或存储过程的命令对象。
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DbCommand GetCommand(string sql)
        {
            return GetCommand(ConnectionString, sql);
        }
        /// <summary>
        /// 获取对数据源执行的SQL语句或存储过程的命令对象。
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DbCommand GetCommand(string connectionString, string sql)
        {
            DbConnection connection = GetConnection(connectionString);
            return GetCommand(connection, sql);
        }
        /// <summary>
        /// 获取对数据源执行的SQL语句或存储过程的命令对象。
        /// </summary>
        /// <param name="connection">到数据库的连接</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DbCommand GetCommand(this DbConnection connection, string sql)
        {
            return GetCommand(connection, sql);
        }
        public static DbCommand GetCommand(this DbConnection connection, string sql, params DbParameter[] parameters)
        {
            return GetCommand(connection, sql, CommandType.Text, parameters);
        }
        public static DbCommand GetCommand(this DbConnection connection, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetCommand(connection, sql, commandType, default(DbTransaction), parameters);
        }
        public static DbCommand GetCommand(this DbConnection connection, string sql, DbTransaction tran, params DbParameter[] parameters)
        {
            return GetCommand(connection, sql, CommandType.Text, tran, parameters);
        }
        public static DbCommand GetCommand(this DbConnection connection, string sql, CommandType commandType, DbTransaction tran, params DbParameter[] parameters)
        {
            if (commandType == CommandType.TableDirect)
                throw new ArgumentOutOfRangeException("When CommandType is TableDirect,Command expect to be typeof OleDbCommand!");
            DbCommand command = null;
            #region DbCommand
            if (connection is SqlConnection)
            {
                if (tran == null)
                    command = new SqlCommand(sql, (SqlConnection)connection);
                else
                    command = new SqlCommand(sql, (SqlConnection)connection, (SqlTransaction)tran);
            }
            else if (connection is OleDbConnection)
            {
                if (tran == null)
                    command = new OleDbCommand(sql, (OleDbConnection)connection);
                else
                    command = new OleDbCommand(sql, (OleDbConnection)connection, (OleDbTransaction)tran);
            }
            else if (connection is MySqlConnection)
            {
                if (tran == null)
                    command = new MySqlCommand(sql, (MySqlConnection)connection);
                else
                    command = new MySqlCommand(sql, (MySqlConnection)connection, (MySqlTransaction)tran);
            }
            else if (connection is OracleConnection)
            {
                if (tran == null)
                    command = new OracleCommand(sql, (OracleConnection)connection);
                else
                    command = new OracleCommand(sql, (OracleConnection)connection, (OracleTransaction)tran);
            }
            #endregion
            if (command != null)
                command.CommandType = commandType;
            if (parameters != null)
                command.Parameters.AddRange(parameters);
            return command;
        }
        #endregion
        #region DataReader
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commond"></param>
        /// <returns></returns>
        public static DbDataReader GetDataReader(this DbCommand commond)
        {
            if (commond != null)
                return commond.ExecuteReader();
            else
                throw new NullReferenceException("DbCommand is Null");
        }
        public static DbDataReader GetDataReader(string connectionString, string sql)
        {
            DbCommand command = GetCommand(connectionString, sql);
            if (command != null)
                return command.ExecuteReader();
            else
                throw new NullReferenceException("DbCommand");
        }
        public static DbDataReader GetDataReader(this DbConnection connection, string sql)
        {
            return GetDataReader(connection, sql, default(DbParameter));
        }
        public static DbDataReader GetDataReader(this DbConnection connection, string sql, params DbParameter[] parameters)
        {
            return GetDataReader(connection, sql, CommandType.Text, parameters);
        }
        public static DbDataReader GetDataReader(this DbConnection connection, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            DbCommand command = GetCommand(connection, sql, commandType, default(DbTransaction), parameters);
            if (command != null)
                return command.ExecuteReader();
            else
                throw new NullReferenceException("DbCommand is Null");
        }
        #endregion
        #region DataAdapter
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commond"></param>
        /// <returns></returns>
        public static DbDataAdapter GetDataAdapter(this DbCommand commond)
        {
            DbDataAdapter dataReader = null;
            if (commond is SqlCommand)
                dataReader = new SqlDataAdapter((SqlCommand)commond);
            else if (commond is MySqlCommand)
                dataReader = new MySqlDataAdapter((MySqlCommand)commond);
            else if (commond is OleDbCommand)
                dataReader = new OleDbDataAdapter((OleDbCommand)commond);
            else if (commond is OracleCommand)
                dataReader = new OracleDataAdapter((OracleCommand)commond);
            return dataReader;
        }
        public static DbDataAdapter GetDataAdapter(string connectstring, string sql)
        {
            DbCommand command = GetCommand(connectstring, sql);
            return GetDataAdapter(command);
        }
        public static DbDataAdapter GetDataAdapter(this DbConnection connection, string sql)
        {
            return GetDataAdapter(connection, sql);
        }
        public static DbDataAdapter GetDataAdapter(this DbConnection connection, string sql, params DbParameter[] parameters)
        {
            return GetDataAdapter(connection, sql, CommandType.Text, parameters);
        }
        public static DbDataAdapter GetDataAdapter(this DbConnection connection, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetDataAdapter(connection, sql, commandType, default(DbTransaction), parameters);
        }
        public static DbDataAdapter GetDataAdapter(this DbConnection connection, string sql, DbTransaction tran, params DbParameter[] parameters)
        {
            return GetDataAdapter(connection, sql, CommandType.Text, tran, parameters);
        }
        public static DbDataAdapter GetDataAdapter(this DbConnection connection, string sql, CommandType commandType, DbTransaction tran, params DbParameter[] parameters)
        {
            DbCommand commond = GetCommand(connection, sql, commandType, tran, parameters);
            return GetDataAdapter(commond);
        }
        #endregion
        #region MyRegion
        /// <summary>
        /// 属性赋值
        /// </summary>
        /// <param name="property">要进行设置的属性</param>
        /// <param name="target">将设置属性值的对象</param>
        /// <param name="obj">该属性的新值</param>
        public static void SetVauleEx(this PropertyInfo property, object target, Object newValue)
        {
            if (property != null)
            {
                //目标对象的属性的类型
                Type propertyType = property.PropertyType;
                //该属性新值当前类型
                Type newValueType = newValue.GetType();
                if (newValue == DBNull.Value || string.IsNullOrEmpty(newValue + ""))
                    property.SetValue(target, null, null);
                //表示此属性类型为基元类型
                else if (propertyType.IsPrimitive || propertyType.IsValueType)
                {
                    //表示此属性为枚举类型
                    if (propertyType.IsEnum)
                    {
                        try
                        {
                            // if (Enum.IsDefined(propertyType, newValue))
                            property.SetValue(target, Enum.Parse(propertyType, newValue + "", true), null);
                        }
                        catch
                        {
                            throw new TargetException(string.Format("Enum {0} doesn't exists value {1}", propertyType.Name, newValue));
                        }
                    }
                    else
                        property.SetValue(target, Convert.ChangeType(newValue, propertyType), null);
                }
                else if (propertyType.IsGenericType && propertyType.IsInterface == false)
                {
                    //Type listType = propertyType.GetGenericTypeDefinition();
                    //property.SetValue(target, Activator.CreateInstance(listType, newValue), null);
                }
                else if (propertyType == typeof(Color))
                {
                    try
                    {
                        property.SetValue(target, Color.FromName(newValue.ToString()), null);
                    }
                    catch { }
                }
                else
                    property.SetValue(target, newValue, null);
            }
        }
        /// <summary>
        /// 从一个对象中取指定的属性
        /// </summary>
        /// <param name="t"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static PropertyInfo GetPropertyEx(this object obj, string propertyName)
        {
            try
            {
                PropertyInfo property = obj.GetType().GetProperty(
                  propertyName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
                //if (property == null)
                //    throw new NullReferenceException();
                //else
                return property;
            }
            catch (Exception ex)
            {

                throw new Exception("获取属性失败：", ex);
            }
        }
        /// <summary>
        /// 从有记录的读取器中读取当前一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <param name="columnCount"></param>
        /// <returns></returns>
        private static T GetValueEx<T>(this DbDataReader reader, int columnCount)
        {
            T element = Activator.CreateInstance<T>();
            for (int i = 0; i < columnCount; i++)
            {
                string columnName = reader.GetName(i);
                PropertyInfo property = element.GetPropertyEx(columnName);
                property.SetVauleEx(element, reader.GetValue(i));
            }
            return element;
        }
        /// <summary>
        /// 从有记录的读取器中读取当前一行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <param name="columnCount"></param>
        /// <returns></returns>
        private static T GetValueEx<T>(this DbDataReader reader, int columnCount, string columnName, ref string key)
        {
            T element = Activator.CreateInstance<T>();
            for (int i = 0; i < columnCount; i++)
            {
                string name = reader.GetName(i);
                object obj = reader.GetValue(i);
                PropertyInfo property = typeof(T).GetProperty(
                    name, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
                property.SetVauleEx(element, obj);
                if (name.Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    key = obj.ToString();
            }
            return element;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        internal static T GetEntity<T>(this DbDataReader reader)
        {
            try
            {
                using (reader)
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        return reader.GetValueEx<T>(reader.FieldCount);
                    }
                    else
                        return default(T);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        internal static List<T> GetList<T>(this DbDataReader reader)
        {
            try
            {
                List<T> lists = new List<T>();
                using (reader)
                {
                    int columnCount = reader.FieldCount;
                    while (reader.Read())
                    {
                        lists.Add(reader.GetValueEx<T>(columnCount));
                    }
                }
                return lists;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        internal static BindingList<T> GetBindingList<T>(this DbDataReader reader)
        {
            try
            {
                BindingList<T> lists = new BindingList<T>();
                using (reader)
                {
                    int columnCount = reader.FieldCount;
                    while (reader.Read())
                    {
                        lists.Add(reader.GetValueEx<T>(columnCount));
                    }
                }
                return lists;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 表数据按指定列进行数据分组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="KeyColumnName">用来分组的列名</param>
        /// <returns></returns>
        internal static Dictionary<string, List<T>> GetDictionary<T>(this DbDataReader reader, string KeyColumnName)
        {
            try
            {
                using (reader)
                {
                    Dictionary<string, List<T>> list = new Dictionary<string, List<T>>();
                    int columnCount = reader.FieldCount;
                    while (reader.Read())
                    {
                        string key = string.Empty;
                        T element = reader.GetValueEx<T>(columnCount, KeyColumnName, ref key);
                        if (list.ContainsKey(key))
                            list[key].Add(element);
                        else
                        {
                            List<T> ls = new List<T>();
                            ls.Add(element);
                            list.Add(key, ls);
                        }
                    }
                    return list;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region GetEntity
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T GetEntity<T>(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetEntity<T>(ConnectionString, sql, commandType, parameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectstring"></param>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static T GetEntity<T>(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataReader dataReader = GetDataReader(connection, sql, commandType, parameters);
                    return dataReader.GetEntity<T>();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region List
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> GetList<T>(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetList<T>(ConnectionString, sql, commandType, parameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectstring"></param>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> GetList<T>(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataReader dataReader = GetDataReader(connection, sql, commandType, parameters);
                    return dataReader.GetList<T>();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region BindingList
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static BindingList<T> GetBindingList<T>(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetBindingList<T>(ConnectionString, sql, commandType, parameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectstring"></param>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static BindingList<T> GetBindingList<T>(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataReader dataReader = GetDataReader(connection, sql, commandType, parameters);
                    return dataReader.GetBindingList<T>();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region ExecuteScalar
        /// <summary>
        /// 执行查询，并返回查询数据第一行第一列，其他行忽略
        /// </summary>
        /// <param name="sql">指定如何解释命令字符串</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params DbParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }
        public static object ExecuteScalar(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return ExecuteScalar(sql, commandType, IsolationLevel.Serializable, parameters);
        }
        public static object ExecuteScalar(string sql, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, isolationLevel, parameters);
        }
        public static object ExecuteScalar(string sql, CommandType commandType, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteScalar(ConnectionString, sql, commandType, isolationLevel, parameters);
        }

        public static object ExecuteScalar(string connectstring, string sql)
        {
            return ExecuteScalar(connectstring, sql, default(DbParameter));
        }
        public static object ExecuteScalar(string connectstring, string sql, params DbParameter[] parameters)
        {
            return ExecuteScalar(connectstring, sql, CommandType.Text, parameters);
        }
        public static object ExecuteScalar(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return ExecuteScalar(connectstring, sql, commandType, IsolationLevel.Serializable, parameters);
        }
        public static object ExecuteScalar(string connectstring, string sql, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteScalar(connectstring, sql, CommandType.Text, isolationLevel, parameters);
        }
        public static object ExecuteScalar(string connectstring, string sql, CommandType commandType, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            DbTransaction dbTransaction = null;
            try
            {
                using (DbConnection connection = GetConnection(connectstring))
                {
                    dbTransaction = connection.BeginTransaction(isolationLevel);
                    connection.Open();
                    DbCommand command = GetCommand(connection, sql, commandType, dbTransaction, parameters);
                    object obj = command.ExecuteScalar();
                    dbTransaction.Commit();
                    return obj;
                }
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                    dbTransaction.Rollback();
                throw ex;
            }
        }
        #endregion
        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string sql, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }
        public static int ExecuteNonQuery(string sql, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, isolationLevel, parameters);
        }
        public static int ExecuteNonQuery(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(ConnectionString, sql, commandType, IsolationLevel.Serializable, parameters);
        }
        public static int ExecuteNonQuery(string sql, CommandType commandType, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(ConnectionString, sql, commandType, isolationLevel, parameters);
        }

        public static int ExecuteNonQuery(string connectstring, string sql)
        {
            return ExecuteNonQuery(connectstring, sql, default(DbParameter));
        }
        public static int ExecuteNonQuery(string connectstring, string sql, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(connectstring, sql, CommandType.Text, IsolationLevel.Serializable, parameters);
        }
        public static int ExecuteNonQuery(string connectstring, string sql, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(connectstring, sql, CommandType.Text, isolationLevel, parameters);
        }
        public static int ExecuteNonQuery(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return ExecuteNonQuery(connectstring, sql, commandType, IsolationLevel.Serializable, parameters);
        }
        public static int ExecuteNonQuery(string connectstring, string sql, CommandType commandType, IsolationLevel isolationLevel, params DbParameter[] parameters)
        {
            DbTransaction dbTransaction = null;
            try
            {
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    dbTransaction = connection.BeginTransaction(isolationLevel);
                    DbCommand command = GetCommand(connection, sql, commandType, dbTransaction, parameters);
                    int count = command.ExecuteNonQuery();
                    dbTransaction.Commit();
                    return count;
                }
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                    dbTransaction.Rollback();
                throw ex;
            }

        }
        #endregion
        #region GetTableSchema
        public static DataTable GetTableSchema(string sql, params DbParameter[] parameters)
        {
            return GetTableSchema(sql, CommandType.Text, parameters);
        }
        public static DataTable GetTableSchema(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetTableSchema(ConnectionString, sql, commandType, parameters);
        }
        public static DataTable GetTableSchema(string connectstring, string sql)
        {
            return GetTableSchema(connectstring, sql);
        }
        public static DataTable GetTableSchema(string connectstring, string sql, params DbParameter[] parameters)
        {
            return GetTableSchema(connectstring, sql, CommandType.Text, parameters);
        }
        public static DataTable GetTableSchema(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                DataTable table = new DataTable();
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataAdapter dataAdapter = GetDataAdapter(connection, sql, commandType, null, parameters);
                    //dataAdapter.Fill(table);
                    dataAdapter.FillSchema(table, SchemaType.Mapped);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion
        #region DataTable
        public static DataTable GetTable(string sql, params DbParameter[] parameters)
        {
            return GetTable(sql, CommandType.Text, parameters);
        }
        public static DataTable GetTable(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetTable(ConnectionString, sql, commandType, parameters);
        }
        public static DataTable GetTable(string connectstring, string sql)
        {
            return GetTable(connectstring, sql);
        }
        public static DataTable GetTable(string connectstring, string sql, params DbParameter[] parameters)
        {
            return GetTable(connectstring, sql, CommandType.Text, parameters);
        }
        public static DataTable GetTable(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                DataTable table = new DataTable();
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataAdapter dataAdapter = GetDataAdapter(connection, sql, commandType, null, parameters);
                    dataAdapter.Fill(table);
                    if (commandType == CommandType.Text)
                        dataAdapter.FillSchema(table, SchemaType.Mapped);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion
        #region DataSet
        public static DataSet GetDataSet(string sql, params DbParameter[] parameters)
        {
            return GetDataSet(sql, CommandType.Text, parameters);
        }
        public static DataSet GetDataSet(string sql, CommandType commandType, params DbParameter[] parameters)
        {
            return GetDataSet(ConnectionString, sql, commandType, parameters);
        }
        public static DataSet GetDataSet(string connectstring, string sql)
        {
            return GetDataSet(connectstring, sql, default(DbParameter));
        }
        public static DataSet GetDataSet(string connectstring, string sql, params DbParameter[] parameters)
        {
            return GetDataSet(connectstring, sql, CommandType.Text, parameters);
        }
        public static DataSet GetDataSet(string connectstring, string sql, CommandType commandType, params DbParameter[] parameters)
        {
            try
            {
                DataSet dateSet = new DataSet();
                using (DbConnection connection = GetConnection(connectstring))
                {
                    connection.Open();
                    DbDataAdapter dataAdapter = GetDataAdapter(connection, sql, commandType, null, parameters);
                    dataAdapter.Fill(dateSet);
                    if (commandType == CommandType.Text)
                        dataAdapter.FillSchema(dateSet, SchemaType.Mapped);
                }
                return dateSet;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion
        /// <summary>
        /// 
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="outParaName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<object> ExecProcOutput(string procName, ReturnType returnType, params DbParameter[] parameters)
        {
            DbTransaction dbTransaction = null;
            try
            {
                List<object> results = new List<object>();
                /*
                 * 返回值
                 */
                object execResult = null;
                DataTable table = new DataTable();
                /*
                 * 输出参数 键值对
                 */
                Dictionary<string, object> outputResults = new Dictionary<string, object>();

                using (DbConnection connection = GetConnection(ConnectionString))
                {
                    connection.Open();
                    dbTransaction = connection.BeginTransaction();
                    DbCommand command = GetCommand(connection, procName, CommandType.StoredProcedure, dbTransaction, parameters);
                    DbDataAdapter adapter = command.GetDataAdapter();
                    switch (returnType)
                    {
                        case ReturnType.None:
                        case ReturnType.ExecuteNonQuery:
                            execResult = command.ExecuteNonQuery();
                            results.Add(execResult);
                            break;
                        case ReturnType.ExecuteScalar:
                            execResult = command.ExecuteScalar();
                            results.Add(execResult);
                            break;
                        case ReturnType.DataTable:
                            adapter.Fill(table);
                            results.Add(table);
                            break;
                        case ReturnType.ForXml:
                            adapter.Fill(table);
                            if (table.Rows.Count > 0)
                                results.Add(table.Rows[0][0].ToString());
                            break;
                        default:
                            break;
                    }
                    dbTransaction.Commit();
                    foreach (DbParameter parameter in command.Parameters)
                    {
                        if (parameter.Direction == ParameterDirection.InputOutput ||
                            parameter.Direction == ParameterDirection.Output)
                            outputResults.Add(parameter.ParameterName, parameter.Value);
                    }
                    if (outputResults.Keys.Count > 0)
                        results.Add(outputResults);
                }
                return results;
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                    dbTransaction.Rollback();
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DbParameter[] GetParameters(Dictionary<string, object> parameters, params string[] outputParameters)
        {
            List<DbParameter> ls = new List<DbParameter>();
            DbParameter parameter = null;
            foreach (string key in parameters.Keys)
            {
                switch (DBType)
                {
                    case DataBaseType.MySql:
                        parameter = new MySqlParameter();
                        break;
                    case DataBaseType.SqlServer:
                        parameter = new SqlParameter();
                        break;
                    case DataBaseType.OleDb:
                        break;
                    case DataBaseType.Oracle:
                        parameter = new OracleParameter();
                        break;
                    default:
                        break;

                }

                if (parameter != null)
                {
                    parameter.ParameterName = key;
                    parameter.Value = parameters[key];
                    if (outputParameters.Contains(key))
                        parameter.Direction = ParameterDirection.Output;
                    ls.Add(parameter);
                }
            }
            return ls.ToArray();
        }
        public static DbParameter[] GetParameters(params DBParameter[] parameters)
        {
            List<DbParameter> ls = new List<DbParameter>();
            DbParameter parameter = null;
            foreach (DBParameter key in parameters)
            {
                switch (DBType)
                {
                    case DataBaseType.MySql:
                        parameter = new MySqlParameter();
                        break;
                    case DataBaseType.SqlServer:
                        parameter = new SqlParameter();
                        break;
                    case DataBaseType.OleDb:
                        break;
                    case DataBaseType.Oracle:
                        parameter = new OracleParameter();
                        break;
                    default:
                        break;
                }
                if (parameter != null)
                {
                    parameter.ParameterName = key.ParameterName;
                    parameter.Value = key.Value;
                    parameter.DbType = key.DbType;
                    if (key.Size == 0)
                        key.Size = int.MaxValue;
                    parameter.Size = key.Size;
                    parameter.Direction = key.Direction;
                    ls.Add(parameter);
                }
            }
            return ls.ToArray();
        }
    }

    [DataContract]
    public enum ReturnType
    {
        [EnumMember]
        None,
        [EnumMember]
        DataTable,
        [EnumMember]
        ForXml,
        [EnumMember]
        ExecuteNonQuery,
        [EnumMember]
        ExecuteScalar
    }
}
